import pymysql
import time
import datetime
# 验证登陆
def check_login_db(usr, passwd):
    # 连接mysql数据库
    db = pymysql.connect(host='localhost',user='root',passwd='yujihao258',db='vchatdb',charset='utf8')
    cursor = db.cursor()
    sql = "select * from login where username ='{}'".format(usr)
    cursor.execute(sql)
    res = cursor.fetchall()
    if len(res) == 0:
        return "查无此人"
    else:
        # i[0]为账号，i[1]为密码，如果没查到 res是空的
        for i in res:
            if passwd == i[1]:
                return "登陆成功"
            else:
                return "密码错误"

# 获取好友列表
def get_all_friends(usr):
    db = pymysql.connect(host='localhost', user='root', passwd='yujihao258', db='vchatdb', charset='utf8')
    cursor = db.cursor()
    sql = """select * from friend_list where username ='{}'""".format(usr)
    cursor.execute(sql)
    res = cursor.fetchall()
    list_all = []
    for i in res:
        tp = (i[2], i[3], i[4],i[5])
        list_all.append(tp)
    return str(list_all)

# 添加一条聊天记录到数据库
def add_one_message(message):
    # message格式为list 包含收发人，内容
    db = pymysql.connect(host='localhost', user='root', passwd='yujihao258', db='vchatdb', charset='utf8')
    cursor = db.cursor()
    whattime = time.time()
    # 写入数据库 时间格式为时间戳
    sql = """insert into chat_notes(username,friend_username,neicong,whattime) values("{}","{}","{}","{}");""".format(message[0], message[1], message[2], str(whattime))
    print(sql)
    cursor.execute(sql)
    db.commit()

# 单向查询两个人直接所有的聊天记录
def find_all_messages(username, to_username):
    db = pymysql.connect(host='localhost', user='root', passwd='yujihao258', db='vchatdb', charset='utf8')
    cursor = db.cursor()
    sql = """select neicong from chat_notes where username ='{}' and friend_username = '{}'""".format(username,to_username)
    cursor.execute(sql)
    res = cursor.fetchall()
    return res

#注册账号
def create_new_id(usr,nicheng,passwd,geqian):
    print(usr, nicheng, passwd, geqian)
    db = pymysql.connect(host='localhost', user='root', passwd='yujihao258', db='vchatdb', charset='utf8')
    cursor = db.cursor()
    sql = "select * from login where username ='{}'".format(usr)
    cursor.execute(sql)
    res = cursor.fetchall()
    if len(res) == 0:
        # 可以注册
        sql = """INSERT INTO login (username,passwd,nicheng,qianming) VALUES("{}","{}","{}","{}")""".format(usr,passwd,nicheng,geqian)
        cursor.execute(sql)
        db.commit()
        add_friend(usr,nicheng,"tulingrobot")
        return "注册成功，请下载app登陆"
    else:
        # 该账号已经存在
        return "该账号已存在"

# 添加好友（包括先查询在将好友信息写入数据库）
def add_friend(username,nicheng,friend_name):
    db = pymysql.connect(host='localhost', user='root', passwd='yujihao258', db='vchatdb', charset='utf8')
    cursor = db.cursor()
    sql = "select * from login where username ='{}'".format(friend_name)
    cursor.execute(sql)
    res = cursor.fetchall()
    if len(res) == 0:
        return "查无此人"
    else:
        # 双向添加为好友 数据库写入两条信息。
        print(res[0])
        sql = """INSERT INTO friend_list (username,nicheng,friend_username,friend_nicheng,friend_qianming) VALUES("{}","{}","{}","{}","{}")""".format(username, nicheng, res[0][0], res[0][2], res[0][3])
        print(sql)
        cursor.execute(sql)
        db.commit()
        sql_find_nicheng = "select * from login where username ='{}'".format(username)
        cursor.execute(sql_find_nicheng)
        res2 = cursor.fetchall()
        print(res)
        sql_double = """INSERT INTO friend_list (username,nicheng,friend_username,friend_nicheng,friend_qianming) VALUES("{}","{}","{}","{}","{}")""".format(
            friend_name, res[0][2],username, nicheng, res2[0][3])
        cursor.execute(sql_double)
        db.commit()
        return "添加成功，请重启客户端"

def chatlog(username, to_username):
    db = pymysql.connect(host='localhost', user='root', passwd='yujihao258', db='vchatdb', charset='utf8')
    cursor = db.cursor()
    sql = """select username,neicong,whattime from chat_notes where username ='{}'and friend_username = '{}' or username ='{}'and friend_username = '{}'""".format(username,to_username,to_username,username)
    print(sql)
    cursor.execute(sql)
    res = cursor.fetchall()
    ls_all = []
    for i in res:
        messge = list(i)
        messge[2] = datetime.datetime.fromtimestamp(int(str(i[2]).split(".")[0])).strftime("%Y-%m-%d %H:%M:%S")
        ls_all.append(messge)
    return ls_all


if __name__ == '__main__':
    add_friend("15133854715","sapple","17849327316")
    # add_one_message(["15133854715", "root", "在线"])
    # res = chatlog("15133854715", "17849327316")
    # print(res)
    # create_new_id("1234","1234","1234","1234")

    # print(get_all_friends("15133854715"))